
<!-- TOC -->

- [7.1. Обзор](#71-%d0%9e%d0%b1%d0%b7%d0%be%d1%80)
- [7.2. Табличные выражения](#72-%d0%a2%d0%b0%d0%b1%d0%bb%d0%b8%d1%87%d0%bd%d1%8b%d0%b5-%d0%b2%d1%8b%d1%80%d0%b0%d0%b6%d0%b5%d0%bd%d0%b8%d1%8f)
  - [7.2.1 Предложение FROM](#721-%d0%9f%d1%80%d0%b5%d0%b4%d0%bb%d0%be%d0%b6%d0%b5%d0%bd%d0%b8%d0%b5-from)
    - [7.2.1.1. Соединённые таблицы](#7211-%d0%a1%d0%be%d0%b5%d0%b4%d0%b8%d0%bd%d1%91%d0%bd%d0%bd%d1%8b%d0%b5-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86%d1%8b)
    - [7.2.1.2. Псевдонимы таблиц и столбцов](#7212-%d0%9f%d1%81%d0%b5%d0%b2%d0%b4%d0%be%d0%bd%d0%b8%d0%bc%d1%8b-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86-%d0%b8-%d1%81%d1%82%d0%be%d0%bb%d0%b1%d1%86%d0%be%d0%b2)
    - [7.2.1.3. Подзапросы](#7213-%d0%9f%d0%be%d0%b4%d0%b7%d0%b0%d0%bf%d1%80%d0%be%d1%81%d1%8b)
    - [7.2.1.4. Табличные функции](#7214-%d0%a2%d0%b0%d0%b1%d0%bb%d0%b8%d1%87%d0%bd%d1%8b%d0%b5-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8)
    - [7.2.1.5. Подзапросы LATERAL](#7215-%d0%9f%d0%be%d0%b4%d0%b7%d0%b0%d0%bf%d1%80%d0%be%d1%81%d1%8b-lateral)
  - [7.2.2. Предложение WHERE](#722-%d0%9f%d1%80%d0%b5%d0%b4%d0%bb%d0%be%d0%b6%d0%b5%d0%bd%d0%b8%d0%b5-where)
  - [7.2.3. Предложения GROUP BY и HAVING](#723-%d0%9f%d1%80%d0%b5%d0%b4%d0%bb%d0%be%d0%b6%d0%b5%d0%bd%d0%b8%d1%8f-group-by-%d0%b8-having)
  - [7.2.4. GROUPING SETS, CUBE и ROLLUP](#724-grouping-sets-cube-%d0%b8-rollup)
  - [7.2.5. Обработка оконных функций](#725-%d0%9e%d0%b1%d1%80%d0%b0%d0%b1%d0%be%d1%82%d0%ba%d0%b0-%d0%be%d0%ba%d0%be%d0%bd%d0%bd%d1%8b%d1%85-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b9)
- [Списки выборки](#%d0%a1%d0%bf%d0%b8%d1%81%d0%ba%d0%b8-%d0%b2%d1%8b%d0%b1%d0%be%d1%80%d0%ba%d0%b8)
  - [Элементы списка выборки](#%d0%ad%d0%bb%d0%b5%d0%bc%d0%b5%d0%bd%d1%82%d1%8b-%d1%81%d0%bf%d0%b8%d1%81%d0%ba%d0%b0-%d0%b2%d1%8b%d0%b1%d0%be%d1%80%d0%ba%d0%b8)
  - [Метки столбцов](#%d0%9c%d0%b5%d1%82%d0%ba%d0%b8-%d1%81%d1%82%d0%be%d0%bb%d0%b1%d1%86%d0%be%d0%b2)
  - [DISTINCT](#distinct)
- [Сочетание запросов](#%d0%a1%d0%be%d1%87%d0%b5%d1%82%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b7%d0%b0%d0%bf%d1%80%d0%be%d1%81%d0%be%d0%b2)
- [Сортировка строк](#%d0%a1%d0%be%d1%80%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%ba%d0%b0-%d1%81%d1%82%d1%80%d0%be%d0%ba)
- [LIMIT и OFFSET](#limit-%d0%b8-offset)
- [Списки VALUES](#%d0%a1%d0%bf%d0%b8%d1%81%d0%ba%d0%b8-values)
- [Запросы WITH (Общие табличные выражения)](#%d0%97%d0%b0%d0%bf%d1%80%d0%be%d1%81%d1%8b-with-%d0%9e%d0%b1%d1%89%d0%b8%d0%b5-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%87%d0%bd%d1%8b%d0%b5-%d0%b2%d1%8b%d1%80%d0%b0%d0%b6%d0%b5%d0%bd%d0%b8%d1%8f)
  - [SELECT в WITH](#select-%d0%b2-with)
  - [Изменение данных в WITH](#%d0%98%d0%b7%d0%bc%d0%b5%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d1%85-%d0%b2-with)

<!-- /TOC -->
<https://postgrespro.ru/docs/postgrespro/12/queries>

# [7.1. Обзор](https://postgrespro.ru/docs/postgrespro/12/queries-overview)

Процесс или команда получения данных из базы данных называется запросом. В SQL запросы формулируются с помощью команды SELECT. В общем виде команда SELECT записывается так:

    [WITH запросы_with] SELECT список_выборки FROM табличное_выражение [определение_сортировки]
В следующих разделах подробно описываются список выборки, табличное выражение и определение сортировки. Запросы WITH являются расширенной возможностью PostgreSQL и будут рассмотрены в последнюю очередь.

Простой запрос выглядит так:

    SELECT * FROM table1;
Если предположить, что в базе данных есть таблица table1, эта команда получит все строки с содержимым всех столбцов из table1. (Метод выдачи результата определяет клиентское приложение. Например, программа psql выведет на экране ASCII-таблицу, хотя клиентские библиотеки позволяют извлекать отдельные значения из результата запроса.) Здесь список выборки задан как *, это означает, что запрос должен вернуть все столбцы табличного выражения. В списке выборки можно также указать подмножество доступных столбцов или составить выражения с этими столбцами. Например, если в table1 есть столбцы a, b и c (и возможно, другие), вы можете выполнить следующий запрос:

    SELECT a, b + c FROM table1;
(в предположении, что столбцы b и c имеют числовой тип данных). Подробнее это описано в Разделе 7.3.

FROM table1 — это простейший тип табличного выражения, в котором просто читается одна таблица. Вообще табличные выражения могут быть сложными конструкциями из базовых таблиц, соединений и подзапросов. А можно и вовсе опустить табличное выражение и использовать команду SELECT как калькулятор:

    SELECT 3 * 4;
В этом может быть больше смысла, когда выражения в списке выборки возвращают меняющиеся результаты. Например, можно вызвать функцию так:

    SELECT random();

# [7.2. Табличные выражения](https://postgrespro.ru/docs/postgrespro/12/queries-table-expressions)

Табличное выражение вычисляет таблицу. Это выражение содержит предложение FROM, за которым могут следовать предложения WHERE, GROUP BY и HAVING. Тривиальные табличные выражения просто ссылаются на физическую таблицу, её называют также базовой, но в более сложных выражениях такие таблицы можно преобразовывать и комбинировать самыми разными способами.

Необязательные предложения WHERE, GROUP BY и HAVING в табличном выражении определяют последовательность преобразований, осуществляемых с данными таблицы, полученной в предложении FROM. В результате этих преобразований образуется виртуальная таблица, строки которой передаются списку выборки, вычисляющему выходные строки запроса.

## 7.2.1 Предложение FROM

Предложение FROM образует таблицу из одной или нескольких ссылок на таблицы, разделённых запятыми.

    FROM табличная_ссылка [, табличная_ссылка [, ...]]
Здесь табличной ссылкой может быть имя таблицы (возможно, с именем схемы), производная таблица, например подзапрос, соединение таблиц или сложная комбинация этих вариантов. Если в предложении FROM перечисляются несколько ссылок, для них применяется перекрёстное соединение (то есть декартово произведение их строк; см. ниже). Список FROM преобразуется в промежуточную виртуальную таблицу, которая может пройти через преобразования WHERE, GROUP BY и HAVING, и в итоге определит результат табличного выражения.

Когда в табличной ссылке указывается таблица, являющаяся родительской в иерархии наследования, в результате будут получены строки не только этой таблицы, но и всех её дочерних таблиц. Чтобы выбрать строки только одной родительской таблицы, перед её именем нужно добавить ключевое слово ONLY. Учтите, что при этом будут получены только столбцы указанной таблицы — дополнительные столбцы дочерних таблиц не попадут в результат.

Если же вы не добавляете ONLY перед именем таблицы, вы можете дописать после него *, тем самым указав, что должны обрабатываться и все дочерние таблицы. Практических причин использовать этот синтаксис больше нет, так как поиск в дочерних таблицах теперь производится по умолчанию. Однако эта запись поддерживается для совместимости со старыми версиями.

### 7.2.1.1. Соединённые таблицы
Соединённая таблица ­­­­­­— это таблица, полученная из двух других (реальных или производных от них) таблиц в соответствии с правилами соединения конкретного типа. Общий синтаксис описания соединённой таблицы:

T1 тип_соединения T2 [ условие_соединения ]
Соединения любых типов могут вкладываются друг в друга или объединяться: и T1, и T2 могут быть результатами соединения. Для однозначного определения порядка соединений предложения JOIN можно заключать в скобки. Если скобки отсутствуют, предложения JOIN обрабатываются слева направо.

**Типы соединений**

**Перекрёстное соединение**
T1 CROSS JOIN T2
Соединённую таблицу образуют все возможные сочетания строк из T1 и T2 (т. е. их декартово произведение), а набор её столбцов объединяет в себе столбцы T1 со следующими за ними столбцами T2. Если таблицы содержат N и M строк, соединённая таблица будет содержать N * M строк.

FROM T1 CROSS JOIN T2 эквивалентно FROM T1 INNER JOIN T2 ON TRUE (см. ниже). Эта запись также эквивалентна FROM T1, T2.

> Примечание
Последняя запись не полностью эквивалентна первым при указании более чем двух таблиц, так как JOIN связывает таблицы сильнее, чем запятая. Например, FROM T1 CROSS JOIN T2 INNER JOIN T3 ON условие не равнозначно FROM T1, T2 INNER JOIN T3 ON условие, так как условие может ссылаться на T1 в первом случае, но не во втором.

**Соединения с сопоставлениями строк**

    T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
    ON логическое_выражение
    T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
    USING ( список столбцов соединения )
    T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
Слова INNER и OUTER необязательны во всех формах. По умолчанию подразумевается INNER (внутреннее соединение), а при указании LEFT, RIGHT и FULL — внешнее соединение.

Условие соединения указывается в предложении ON или USING, либо неявно задаётся ключевым словом NATURAL. Это условие определяет, какие строки двух исходных таблиц считаются «соответствующими» друг другу (это подробно рассматривается ниже).

Возможные типы соединений с сопоставлениями строк:

**INNER JOIN**
Для каждой строки R1 из T1 в результирующей таблице содержится строка для каждой строки в T2, удовлетворяющей условию соединения с R1.

**LEFT OUTER JOIN**
Сначала выполняется внутреннее соединение (INNER JOIN). Затем в результат добавляются все строки из T1, которым не соответствуют никакие строки в T2, а вместо значений столбцов T2 вставляются NULL. Таким образом, в результирующей таблице всегда будет минимум одна строка для каждой строки из T1.

**RIGHT OUTER JOIN**
Сначала выполняется внутреннее соединение (INNER JOIN). Затем в результат добавляются все строки из T2, которым не соответствуют никакие строки в T1, а вместо значений столбцов T1 вставляются NULL. Это соединение является обратным к левому (LEFT JOIN): в результирующей таблице всегда будет минимум одна строка для каждой строки из T2.

**FULL OUTER JOIN**
Сначала выполняется внутреннее соединение. Затем в результат добавляются все строки из T1, которым не соответствуют никакие строки в T2, а вместо значений столбцов T2 вставляются NULL. И наконец, в результат включаются все строки из T2, которым не соответствуют никакие строки в T1, а вместо значений столбцов T1 вставляются NULL.

Предложение ON определяет наиболее общую форму условия соединения: в нём указываются выражения логического типа, подобные тем, что используются в предложении WHERE. Пара строк из T1 и T2 соответствуют друг другу, если выражение ON возвращает для них true.

USING — это сокращённая запись условия, полезная в ситуации, когда с обеих сторон соединения столбцы имеют одинаковые имена. Она принимает список общих имён столбцов через запятую и формирует условие соединения с равенством этих столбцов. Например, запись соединения T1 и T2 с USING (a, b) формирует условие ON T1.a = T2.a AND T1.b = T2.b.

Более того, при выводе JOIN USING исключаются избыточные столбцы: оба сопоставленных столбца выводить не нужно, так как они содержат одинаковые значения. Тогда как JOIN ON выдаёт все столбцы из T1, а за ними все столбцы из T2, JOIN USING выводит один столбец для каждой пары (в указанном порядке), за ними все оставшиеся столбцы из T1 и, наконец, все оставшиеся столбцы T2.

Наконец, NATURAL — сокращённая форма USING: она образует список USING из всех имён столбцов, существующих в обеих входных таблицах. Как и с USING, эти столбцы оказываются в выходной таблице в единственном экземпляре. Если столбцов с одинаковыми именами не находится, NATURAL JOIN действует как JOIN ... ON TRUE и выдаёт декартово произведение строк.

> Примечание
Предложение USING разумно защищено от изменений в соединяемых отношениях, так как оно связывает только явно перечисленные столбцы. NATURAL считается более рискованным, так как при любом изменении схемы в одном или другом отношении, когда появляются столбцы с совпадающими именами, при соединении будут связываться и эти новые столбцы.

Для наглядности предположим, что у нас есть таблицы t1:

    num | name
    -----+------
    1 | a
    2 | b
    3 | c
    и t2:

    num | value
    -----+-------
    1 | xxx
    3 | yyy
    5 | zzz
С ними для разных типов соединений мы получим следующие результаты:

    => SELECT * FROM t1 CROSS JOIN t2;
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    1 | a    |   3 | yyy
    1 | a    |   5 | zzz
    2 | b    |   1 | xxx
    2 | b    |   3 | yyy
    2 | b    |   5 | zzz
    3 | c    |   1 | xxx
    3 | c    |   3 | yyy
    3 | c    |   5 | zzz
    (9 rows)

    => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    3 | c    |   3 | yyy
    (2 rows)

    => SELECT * FROM t1 INNER JOIN t2 USING (num);
    num | name | value
    -----+------+-------
    1 | a    | xxx
    3 | c    | yyy
    (2 rows)

    => SELECT * FROM t1 NATURAL INNER JOIN t2;
    num | name | value
    -----+------+-------
    1 | a    | xxx
    3 | c    | yyy
    (2 rows)

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    2 | b    |     |
    3 | c    |   3 | yyy
    (3 rows)

    => SELECT * FROM t1 LEFT JOIN t2 USING (num);
    num | name | value
    -----+------+-------
    1 | a    | xxx
    2 | b    |
    3 | c    | yyy
    (3 rows)

    => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    3 | c    |   3 | yyy
        |      |   5 | zzz
    (3 rows)

    => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    2 | b    |     |
    3 | c    |   3 | yyy
        |      |   5 | zzz
    (4 rows)
Условие соединения в предложении ON может также содержать выражения, не связанные непосредственно с соединением. Это может быть полезно в некоторых запросах, но не следует использовать это необдуманно. Рассмотрите следующий запрос:

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    2 | b    |     |
    3 | c    |     |
    (3 rows)
Заметьте, что если поместить ограничение в предложение WHERE, вы получите другой результат:

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
    num | name | num | value
    -----+------+-----+-------
    1 | a    |   1 | xxx
    (1 row)
Это связано с тем, что ограничение, помещённое в предложение ON, обрабатывается до операции соединения, тогда как ограничение в WHERE — после. Это не имеет значения при внутренних соединениях, но важно при внешних.

### 7.2.1.2. Псевдонимы таблиц и столбцов
Таблицам и ссылкам на сложные таблицы в запросе можно дать временное имя, по которому к ним можно будет обращаться в рамках запроса. Такое имя называется псевдонимом таблицы.

Определить псевдоним таблицы можно, написав

FROM табличная_ссылка AS псевдоним
или

FROM табличная_ссылка псевдоним
Ключевое слово AS является необязательным. Вместо псевдоним здесь может быть любой идентификатор.

Псевдонимы часто применяются для назначения коротких идентификаторов длинным именам таблиц с целью улучшения читаемости запросов. Например:

    SELECT * FROM "очень_длинное_имя_таблицы" s JOIN "другое_длинное_имя" a
    ON s.id = a.num;
Псевдоним становится новым именем таблицы в рамках текущего запроса, т. е. после назначения псевдонима использовать исходное имя таблицы в другом месте запроса нельзя. Таким образом, следующий запрос недопустим:

    SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- неправильно
Хотя в основном псевдонимы используются для удобства, они бывают необходимы, когда таблица соединяется сама с собой, например:

    SELECT * FROM people AS mother JOIN people AS child
     ON mother.id = child.mother_id;
Кроме того, псевдонимы обязательно нужно назначать подзапросам (см. Подраздел 7.2.1.3).

В случае неоднозначности определения псевдонимов можно использовать скобки. В следующем примере первый оператор назначает псевдоним b второму экземпляру my_table, а второй оператор назначает псевдоним результату соединения:

    SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
    SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
В другой форме назначения псевдонима временные имена даются не только таблицам, но и её столбцам:

     FROM табличная_ссылка [AS] псевдоним ( столбец1 [, столбец2 [, ...]] )
Если псевдонимов столбцов оказывается меньше, чем фактически столбцов в таблице, остальные столбцы сохраняют свои исходные имена. Эта запись особенно полезна для замкнутых соединений или подзапросов.

Когда псевдоним применяется к результату JOIN, он скрывает оригинальные имена таблиц внутри JOIN. Например, это допустимый SQL-запрос:

    SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
а запрос:

    SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
ошибочный, так как псевдоним таблицы a не виден снаружи определения псевдонима c.

### 7.2.1.3. Подзапросы
Подзапросы, образующие таблицы, должны заключаться в скобки и им обязательно должны назначаться псевдонимы (как описано в Подразделе 7.2.1.2). Например:

    FROM (SELECT * FROM table1) AS псевдоним
Этот пример равносилен записи FROM table1 AS псевдоним. Более интересные ситуации, которые нельзя свести к простому соединению, возникают, когда в подзапросе используются агрегирующие функции или группировка.

Подзапросом может также быть список VALUES:

    FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
        AS names(first, last)
Такому подзапросу тоже требуется псевдоним. Назначать псевдонимы столбцам списка VALUES не требуется, но вообще это хороший приём. Подробнее это описано в Разделе 7.7.

### 7.2.1.4. Табличные функции
Табличные функции — это функции, выдающие набор строк, содержащих либо базовые типы данных (скалярных типов), либо составные типы (табличные строки). Они применяются в запросах как таблицы, представления или подзапросы в предложении FROM. Столбцы, возвращённые табличными функциями, можно включить в выражения SELECT, JOIN или WHERE так же, как столбцы таблиц, представлений или подзапросов.

Табличные функции можно также скомбинировать, используя запись ROWS FROM. Результаты функций будут возвращены в параллельных столбцах; число строк в этом случае будет наибольшим из результатов всех функций, а результаты функций с меньшим количеством строк будут дополнены значениями NULL.

    вызов_функции [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ...])]]
    ROWS FROM( вызов_функции [, ...] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ...])]]
Если указано предложение WITH ORDINALITY, к столбцам результатов функций будет добавлен ещё один, с типом bigint. В этом столбце нумеруются строки результирующего набора, начиная с 1. (Это обобщение стандартного SQL-синтаксиса UNNEST ... WITH ORDINALITY.) По умолчанию, этот столбец называется ordinality, но ему можно присвоить и другое имя с помощью указания AS.

Специальную табличную функцию UNNEST можно вызвать с любым числом параметров-массивов, а возвращает она соответствующее число столбцов, как если бы UNNEST (Раздел 9.18) вызывалась для каждого параметра в отдельности, а результаты объединялись с помощью конструкции ROWS FROM.

    UNNEST( выражение_массива [, ...] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ...])]]
Если псевдоним_таблицы не указан, в качестве имени таблицы используется имя функции; в случае с конструкцией ROWS FROM() — имя первой функции.

Если псевдонимы столбцов не указаны, то для функции, возвращающей базовый тип данных, именем столбца будет имя функции. Для функций, возвращающих составной тип, имена результирующих столбцов определяются индивидуальными атрибутами типа.

Несколько примеров:

    CREATE TABLE foo (fooid int, foosubid int, fooname text);

    CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
    $$ LANGUAGE SQL;

    SELECT * FROM getfoo(1) AS t1;

    SELECT * FROM foo
        WHERE foosubid IN (
                            SELECT foosubid
                            FROM getfoo(foo.fooid) z
                            WHERE z.fooid = foo.fooid
                        );

    CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

    SELECT * FROM vw_getfoo;
В некоторых случаях бывает удобно определить табличную функцию, возвращающую различные наборы столбцов при разных вариантах вызова. Для этого нужно указать, что она возвращает псевдотип record. Используя такую функцию, ожидаемую структуру строк нужно описать в самом запросе, чтобы система знала, как разобрать запрос и составить его план. Записывается это так:

    вызов_функции [AS] псевдоним (определение_столбца [, ...])
    вызов_функции AS [псевдоним] (определение_столбца [, ...])
    ROWS FROM( ... вызов_функции AS (определение_столбца [, ...]) [, ...] )
Без ROWS FROM() список определения_столбцов заменяет список псевдонимов, который можно также добавить в предложении FROM; имена в определениях столбцов служат псевдонимами. С ROWS FROM() список определения_столбцов можно добавить к каждой функции отдельно, либо в случае с одной функцией и без предложения WITH ORDINALITY, список определения_столбцов можно записать вместо списка с псевдонимами столбцов после ROWS FROM().

Взгляните на этот пример:

    SELECT *
        FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
        AS t1(proname name, prosrc text)
        WHERE proname LIKE 'bytea%';
Здесь функция dblink (из модуля dblink) выполняет удалённый запрос. Она объявлена как функция, возвращающая тип record, так как он подойдёт для запроса любого типа. В этом случае фактический набор столбцов функции необходимо описать в вызывающем её запросе, чтобы анализатор запроса знал, например, как преобразовать *.

### 7.2.1.5. Подзапросы LATERAL
Перед подзапросами в предложении FROM можно добавить ключевое слово LATERAL. Это позволит ссылаться в них на столбцы предшествующих элементов списка FROM. (Без LATERAL каждый подзапрос выполняется независимо и поэтому не может обращаться к другим элементам FROM.)

Перед табличными функциями в предложении FROM также можно указать LATERAL, но для них это ключевое слово необязательно; в аргументах функций в любом случае можно обращаться к столбцам в предыдущих элементах FROM.

Элемент LATERAL может находиться на верхнем уровне списка FROM или в дереве JOIN. В последнем случае он может также ссылаться на любые элементы в левой части JOIN, справа от которого он находится.

Когда элемент FROM содержит ссылки LATERAL, запрос выполняется следующим образом: сначала для строки элемента FROM с целевыми столбцами, или набора строк из нескольких элементов FROM, содержащих целевые столбцы, вычисляется элемент LATERAL со значениями этих столбцов. Затем результирующие строки обычным образом соединяются со строками, из которых они были вычислены. Эта процедура повторяется для всех строк исходных таблиц.

LATERAL можно использовать так:

    SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
Здесь это не очень полезно, так как тот же результат можно получить более простым и привычным способом:

    SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
Применять LATERAL имеет смысл в основном, когда для вычисления соединяемых строк необходимо обратиться к столбцам других таблиц. В частности, это полезно, когда нужно передать значение функции, возвращающей набор данных. Например, если предположить, что vertices(polygon) возвращает набор вершин многоугольника, близкие вершины многоугольников из таблицы polygons можно получить так:

    SELECT p1.id, p2.id, v1, v2
    FROM polygons p1, polygons p2,
        LATERAL vertices(p1.poly) v1,
        LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
Этот запрос можно записать и так:

    SELECT p1.id, p2.id, v1, v2
    FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
        polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
или переформулировать другими способами. (Как уже упоминалось, в данном примере ключевое слово LATERAL не требуется, но мы добавили его для ясности.)

Особенно полезно бывает использовать LEFT JOIN с подзапросом LATERAL, чтобы исходные строки оказывались в результате, даже если подзапрос LATERAL не возвращает строк. Например, если функция get_product_names() выдаёт названия продуктов, выпущенных определённым производителем, но о продукции некоторых производителей информации нет, мы можем найти, каких именно, примерно так:

    SELECT m.name
    FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
    WHERE pname IS NULL;

## 7.2.2. Предложение WHERE

Предложение WHERE записывается так:

WHERE условие_ограничения
где условие_ограничения — любое выражение значения (см. Раздел 4.2), выдающее результат типа boolean.

После обработки предложения FROM каждая строка полученной виртуальной таблицы проходит проверку по условию ограничения. Если результат условия равен true, эта строка остаётся в выходной таблице, а иначе (если результат равен false или NULL) отбрасывается. В условии ограничения, как правило, задействуется минимум один столбец из таблицы, полученной на выходе FROM. Хотя строго говоря, это не требуется, но в противном случае предложение WHERE будет бессмысленным.

> Примечание
Условие для внутреннего соединения можно записать как в предложении WHERE, так и в предложении JOIN. Например, это выражение:

    FROM a, b WHERE a.id = b.id AND b.val > 5
равнозначно этому:

    FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
и возможно, даже этому:

    FROM a NATURAL JOIN b WHERE b.val > 5
Какой вариант выбрать, в основном дело вкуса и стиля. Вариант с JOIN внутри предложения FROM, возможно, не лучший с точки зрения совместимости с другими СУБД, хотя он и описан в стандарте SQL. Но для внешних соединений других вариантов нет: их можно записывать только во FROM. Предложения ON и USING во внешних соединениях не равнозначны условию WHERE, так как они могут добавлять строки (для входных строк без соответствия), а также удалять их из конечного результата.

Несколько примеров запросов с WHERE:

    SELECT ... FROM fdt WHERE c1 > 5

    SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

    SELECT ... FROM fdt WHERE c1 BETWEEN
    (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

    SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
fdt — название таблицы, порождённой в предложении FROM. Строки, которые не соответствуют условию WHERE, исключаются из fdt. Обратите внимание, как в качестве выражений значения используются скалярные подзапросы. Как и любые другие запросы, подзапросы могут содержать сложные табличные выражения. Заметьте также, что fdt используется в подзапросах. Дополнение имени c1 в виде fdt.c1 необходимо только, если в порождённой таблице в подзапросе также оказывается столбец c1. Полное имя придаёт ясность даже там, где без него можно обойтись. Этот пример показывает, как область именования столбцов внешнего запроса распространяется на все вложенные в него внутренние запросы.

## 7.2.3. Предложения GROUP BY и HAVING

Строки порождённой входной таблицы, прошедшие фильтр WHERE, можно сгруппировать с помощью предложения GROUP BY, а затем оставить в результате только нужные группы строк, используя предложение HAVING.

    SELECT список_выборки
        FROM ...
        [WHERE ...]
        GROUP BY группирующий_столбец [, группирующий_столбец]...
Предложение GROUP BY группирует строки таблицы, объединяя их в одну группу при совпадении значений во всех перечисленных столбцах. Порядок, в котором указаны столбцы, не имеет значения. В результате наборы строк с одинаковыми значениями преобразуются в отдельные строки, представляющие все строки группы. Это может быть полезно для устранения избыточности выходных данных и/или для вычисления агрегатных функций, применённых к этим группам. Например:

    => SELECT * FROM test1;
    x | y
    ---+---
    a | 3
    c | 2
    b | 5
    a | 1
    (4 rows)

    => SELECT x FROM test1 GROUP BY x;
    x
    ---
    a
    b
    c
    (3 rows)
Во втором запросе мы не могли написать SELECT * FROM test1 GROUP BY x, так как для столбца y нет единого значения, связанного с каждой группой. Однако столбцы, по которым выполняется группировка, можно использовать в списке выборки, так как они имеют единственное значение в каждой группе.

Вообще говоря, в группированной таблице столбцы, не включённые в список GROUP BY, можно использовать только в агрегатных выражениях. Пример такого агрегатного выражения:

    => SELECT x, sum(y) FROM test1 GROUP BY x;
    x | sum
    ---+-----
    a |   4
    b |   5
    c |   2
    (3 rows)
Здесь sum — агрегатная функция, вычисляющая единственное значение для всей группы. Подробную информацию о существующих агрегатных функциях можно найти в Разделе 9.20.

> Подсказка
Группировка без агрегатных выражений по сути выдаёт набор различающихся значений столбцов. Этот же результат можно получить с помощью предложения DISTINCT (см. Подраздел 7.3.3).

Взгляните на следующий пример: в нём вычисляется общая сумма продаж по каждому продукту (а не общая сумма по всем продуктам):

    SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
        FROM products p LEFT JOIN sales s USING (product_id)
        GROUP BY product_id, p.name, p.price;
В этом примере столбцы product_id, p.name и p.price должны присутствовать в списке GROUP BY, так как они используются в списке выборки. Столбец s.units может отсутствовать в списке GROUP BY, так как он используется только в агрегатном выражении (sum(...)), вычисляющем сумму продаж. Для каждого продукта этот запрос возвращает строку с итоговой суммой по всем продажам данного продукта.

Если бы в таблице products по столбцу product_id был создан первичный ключ, тогда в данном примере было бы достаточно сгруппировать строки по product_id, так как название и цена продукта функционально зависят от кода продукта и можно однозначно определить, какое название и цену возвращать для каждой группы по ID.

В стандарте SQL GROUP BY может группировать только по столбцам исходной таблицы, но расширение Postgres Pro позволяет использовать в GROUP BY столбцы из списка выборки. Также возможна группировка по выражениям, а не просто именам столбцов.

Если таблица была сгруппирована с помощью GROUP BY, но интерес представляют только некоторые группы, отфильтровать их можно с помощью предложения HAVING, действующего подобно WHERE. Записывается это так:

    SELECT список_выборки FROM ... [WHERE ...] GROUP BY ...
    HAVING логическое_выражение
В предложении HAVING могут использоваться и группирующие выражения, и выражения, не участвующие в группировке (в этом случае это должны быть агрегирующие функции).

Пример:

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
    x | sum
    ---+-----
    a |   4
    b |   5
    (2 rows)

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
    x | sum
    ---+-----
    a |   4
    b |   5
    (2 rows)
И ещё один более реалистичный пример:

    SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
        FROM products p LEFT JOIN sales s USING (product_id)
        WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
        GROUP BY product_id, p.name, p.price, p.cost
        HAVING sum(p.price * s.units) > 5000;
В данном примере предложение WHERE выбирает строки по столбцу, не включённому в группировку (выражение истинно только для продаж за последние четыре недели), тогда как предложение HAVING отфильтровывает группы с общей суммой продаж больше 5000. Заметьте, что агрегатные выражения не обязательно должны быть одинаковыми во всех частях запроса.

Если в запросе есть вызовы агрегатных функций, но нет предложения GROUP BY, строки всё равно будут группироваться: в результате окажется одна строка группы (или возможно, ни одной строки, если эта строка будет отброшена предложением HAVING). Это справедливо и для запросов, которые содержат только предложение HAVING, но не содержат вызовы агрегатных функций и предложение GROUP BY.

## 7.2.4. GROUPING SETS, CUBE и ROLLUP

Более сложные, чем описанные выше, операции группировки возможны с концепцией наборов группирования. Данные, выбранные предложениями FROM и WHERE, группируются отдельно для каждого заданного набора группирования, затем для каждой группы вычисляются агрегатные функции как для простых предложений GROUP BY, и в конце возвращаются результаты. Например:

    => SELECT * FROM items_sold;
    brand | size | sales
    -------+------+-------
    Foo   | L    |  10
    Foo   | M    |  20
    Bar   | M    |  15
    Bar   | L    |  5
    (4 rows)

    => SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
    brand | size | sum
    -------+------+-----
    Foo   |      |  30
    Bar   |      |  20
        | L    |  15
        | M    |  35
        |      |  50
    (5 rows)
В каждом внутреннем списке GROUPING SETS могут задаваться ноль или более столбцов или выражений, которые воспринимаются так же, как если бы они были непосредственно записаны в предложении GROUP BY. Пустой набор группировки означает, что все строки сводятся к одной группе (которая выводится, даже если входных строк нет), как описано выше для агрегатных функций без предложения GROUP BY.

Ссылки на группирующие столбцы или выражения заменяются в результирующих строках значениями NULL для тех группирующих наборов, в которых эти столбцы отсутствуют. Чтобы можно было понять, результатом какого группирования стала конкретная выходная строка, предназначена функция, описанная в Таблице 9.59.

Для указания двух распространённых видов наборов группирования предусмотрена краткая запись. Предложение формы

    ROLLUP ( e1, e2, e3, ... )
представляет заданный список выражений и всех префиксов списка, включая пустой список; то есть оно равнозначно записи

    GROUPING SETS (
        ( e1, e2, e3, ... ),
        ...
        ( e1, e2 ),
        ( e1 ),
        ( )
    )
Оно часто применяется для анализа иерархических данных, например, для суммирования зарплаты по отделам, подразделениям и компании в целом.

Предложение формы

    CUBE ( e1, e2, ... )
представляет заданный список и все его возможные подмножества (степень множества). Таким образом, запись

    CUBE ( a, b, c )
равнозначна

    GROUPING SETS (
        ( a, b, c ),
        ( a, b    ),
        ( a,    c ),
        ( a       ),
        (    b, c ),
        (    b    ),
        (       c ),
        (         )
    )
Элементами предложений CUBE и ROLLUP могут быть либо отдельные выражения, либо вложенные списки элементов в скобках. Вложенные списки обрабатываются как атомарные единицы, с которыми формируются отдельные наборы группирования. Например:

    CUBE ( (a, b), (c, d) )
равнозначно

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b       ),
        (       c, d ),
        (            )
    )
    и

    ROLLUP ( a, (b, c), d )
равнозначно

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b, c    ),
        ( a          ),
        (            )
    )
Конструкции CUBE и ROLLUP могут применяться либо непосредственно в предложении GROUP BY, либо вкладываться внутрь предложения GROUPING SETS. Если одно предложение GROUPING SETS вкладывается внутрь другого, результат будет таким же, как если бы все элементы внутреннего предложения были записаны непосредственно во внешнем.

Если в одном предложении GROUP BY задаётся несколько элементов группирования, окончательный список наборов группирования образуется как прямое произведение этих элементов. Например:

    GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
равнозначно

    GROUP BY GROUPING SETS (
        (a, b, c, d), (a, b, c, e),
        (a, b, d),    (a, b, e),
        (a, c, d),    (a, c, e),
        (a, d),       (a, e)
)
> Примечание
Конструкция (a, b) обычно воспринимается в выражениях как конструктор строки. Однако в предложении GROUP BY на верхнем уровне выражений запись (a, b) воспринимается как список выражений, как описано выше. Если вам по какой-либо причине нужен именно конструктор строки в выражении группирования, используйте запись ROW(a, b).

## 7.2.5. Обработка оконных функций

Если запрос содержит оконные функции (см. Раздел 3.5, Раздел 9.21 и Подраздел 4.2.8), эти функции вычисляются после каждой группировки, агрегатных выражений и фильтрации HAVING. Другими словами, если в запросе есть агрегатные функции, предложения GROUP BY или HAVING, оконные функции видят не исходные строки, полученные из FROM/WHERE, а сгруппированные.

Когда используются несколько оконных функций, все оконные функции, имеющие в своих определениях синтаксически равнозначные предложения PARTITION BY и ORDER BY, гарантированно обрабатывают данные за один проход. Таким образом, они увидят один порядок сортировки, даже если ORDER BY не определяет порядок однозначно. Однако относительно функций с разными формулировками PARTITION BY и ORDER BY никаких гарантий не даётся. (В таких случаях между проходами вычислений оконных функций обычно требуется дополнительный этап сортировки и эта сортировка может не сохранять порядок строк, равнозначный с точки зрения ORDER BY.)

В настоящее время оконные функции всегда требуют предварительно отсортированных данных, так что результат запроса будет отсортирован согласно тому или иному предложению PARTITION BY/ORDER BY оконных функций. Однако полагаться на это не следует. Если вы хотите, чтобы результаты сортировались определённым образом, явно добавьте предложение ORDER BY на верхнем уровне запроса.

# Списки выборки

Как говорилось в предыдущем разделе, табличное выражение в SELECT создаёт промежуточную виртуальную таблицу, возможно объединяя таблицы, представления, группируя и исключая лишние строки и т. д. Полученная таблица передаётся для обработки в список выборки. Этот список выбирает, какие столбцы промежуточной таблицы должны выводиться в результате и как именно.

## [Элементы списка выборки](https://postgrespro.ru/docs/postgrespro/12/queries-select-lists)

Простейший список выборки образует элемент *, который выбирает все столбцы из полученного табличного выражения. Список выборки также может содержать список выражений значения через запятую (как определено в Разделе 4.2). Например, это может быть список имён столбцов:

    SELECT a, b, c FROM ...
Имена столбцов a, b и c представляют либо фактические имена столбцов таблиц, перечисленных в предложении FROM, либо их псевдонимы, определённые как описано в Подразделе 7.2.1.2. Пространство имён в списке выборки то же, что и в предложении WHERE, если не используется группировка. В противном случае оно совпадает с пространством имён предложения HAVING.

Если столбец с заданным именем есть в нескольких таблицах, необходимо также указать имя таблицы, например так:

    SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
Обращаясь к нескольким таблицам, бывает удобно получить сразу все столбцы одной из таблиц:

    SELECT tbl1.*, tbl2.a FROM ...
Подробнее запись имя_таблицы.* описывается в Подразделе 8.16.5.

Если в списке выборки используется обычное выражение значения, по сути при этом в возвращаемую таблицу добавляется новый виртуальный столбец. Выражение значения вычисляется один раз для каждой строки результата со значениями столбцов в данной строке. Хотя выражения в списке выборки не обязательно должны обращаться к столбцам табличного выражения из предложения FROM; они могут содержать, например и простые арифметические выражения.

## Метки столбцов

Элементам в списке выборки можно назначить имена для последующей обработки, например, для указания в предложении ORDER BY или для вывода в клиентском приложении. Например:

    SELECT a AS value, b + c AS sum FROM ...
Если выходное имя столбца не определено (с помощью AS), система назначает имя сама. Для простых ссылок на столбцы этим именем становится имя целевого столбца, а для вызовов функций это имя функции. Для сложных выражений система генерирует некоторое подходящее имя.

Слово AS можно опустить, но только если имя нового столбца не является ключевым словом Postgres Pro (см. Приложение C). Во избежание случайного совпадения имени с ключевым словом это имя можно заключить в кавычки. Например, VALUE — ключевое слово, поэтому такой вариант не будет работать:

    SELECT a value, b + c AS sum FROM ...
а такой будет:

    SELECT a "value", b + c AS sum FROM ...
Для предотвращения конфликта с ключевыми словами, которые могут появиться в будущем, рекомендуется всегда писать AS или заключать метки выходных столбцов в кавычки.

> Примечание
Именование выходных столбцов отличается от того, что происходит в предложении FROM (см. Подраздел 7.2.1.2). Один столбец можно переименовать дважды, но на выходе окажется имя, назначенное в списке выборки.

## DISTINCT

После обработки списка выборки в результирующей таблице можно дополнительно исключить дублирующиеся строки. Для этого сразу после SELECT добавляется ключевое слово DISTINCT:

    SELECT DISTINCT список_выборки ...
(Чтобы явно включить поведение по умолчанию, когда возвращаются все строки, вместо DISTINCT можно указать ключевое слово ALL.)

Две строки считаются разными, если они содержат различные значения минимум в одном столбце. При этом значения NULL полагаются равными.

Кроме того, можно явно определить, какие строки будут считаться различными, следующим образом:

    SELECT DISTINCT ON (выражение [, выражение ...]) список_выборки ...
Здесь выражение — обычное выражение значения, вычисляемое для всех строк. Строки, для которых перечисленные выражения дают один результат, считаются дублирующимися и возвращается только первая строка из такого набора. Заметьте, что «первая строка» набора может быть любой, если только запрос не включает сортировку, гарантирующую однозначный порядок строк, поступающих в фильтр DISTINCT. (Обработка DISTINCT ON производится после сортировки ORDER BY.)

Предложение DISTINCT ON не описано в стандарте SQL и иногда его применение считается плохим стилем из-за возможной неопределённости в результатах. При разумном использовании GROUP BY и подзапросов во FROM можно обойтись без этой конструкции, но часто она бывает удобнее.

# [Сочетание запросов](https://postgrespro.ru/docs/postgrespro/12/queries-union)

Результаты двух запросов можно обработать, используя операции над множествами: объединение, пересечение и вычитание. Эти операции записываются соответственно так:

    запрос1 UNION [ALL] запрос2
    запрос1 INTERSECT [ALL] запрос2
    запрос1 EXCEPT [ALL] запрос2
Здесь запрос1 и запрос2 — это запросы, в которых могут использоваться все возможности, рассмотренные до этого. Операции над множествами тоже можно вкладывать и соединять, например:

    запрос1 UNION запрос2 UNION запрос3
Этот сложный запрос выполняется так:

    (запрос1 UNION запрос2) UNION запрос3
**UNION** по сути добавляет результаты второго запроса к результатам первого (хотя никакой порядок возвращаемых строк при этом не гарантируется). Более того, эта операция убирает дублирующиеся строки из результата так же, как это делает DISTINCT, если только не указано UNION ALL.

**INTERSECT** возвращает все строки, содержащиеся в результате и первого, и второго запроса. Дублирующиеся строки отфильтровываются, если не указано ALL.

**EXCEPT** возвращает все строки, которые есть в результате первого запроса, но отсутствуют в результате второго. (Иногда это называют разницей двух запросов.) И здесь дублирующиеся строки отфильтровываются, если не указано ALL.

Чтобы можно было вычислить объединение, пересечение или разницу результатов двух запросов, эти запросы должны быть «совместимыми для объединения», что означает, что они должны иметь одинаковое число столбцов и соответствующие столбцы должны быть совместимых типов, как описывается в Разделе 10.5.

# [Сортировка строк](https://postgrespro.ru/docs/postgrespro/12/queries-order)

После того как запрос выдал таблицу результатов (после обработки списка выборки), её можно отсортировать. Если сортировка не задана, строки возвращаются в неопределённом порядке. Фактический порядок строк в этом случае будет зависеть от плана соединения и сканирования, а также от порядка данных на диске, поэтому полагаться на него нельзя. Определённый порядок выводимых строк гарантируется, только если этап сортировки задан явно.

Порядок сортировки определяет предложение ORDER BY:

    SELECT список_выборки
        FROM табличное_выражение
        ORDER BY выражение_сортировки1 [ASC | DESC] [NULLS { FIRST | LAST }]
                [, выражение_сортировки2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]
Выражениями сортировки могут быть любые выражения, допустимые в списке выборки запроса. Например:

    SELECT a, b FROM table1 ORDER BY a + b, c;
Когда указывается несколько выражений, последующие значения позволяют отсортировать строки, в которых совпали все предыдущие значения. Каждое выражение можно дополнить ключевыми словами ASC или DESC, которые выбирают сортировку соответственно по возрастанию или убыванию. По умолчанию принят порядок по возрастанию (ASC). При сортировке по возрастанию сначала идут меньшие значения, где понятие «меньше» определяется оператором <. Подобным образом, сортировка по возрастанию определяется оператором >. [5]

Для определения места значений NULL можно использовать указания NULLS FIRST и NULLS LAST, которые помещают значения NULL соответственно до или после значений не NULL. По умолчанию значения NULL считаются больше любых других, то есть подразумевается NULLS FIRST для порядка DESC и NULLS LAST в противном случае.

Заметьте, что порядки сортировки определяются независимо для каждого столбца. Например, ORDER BY x, y DESC означает ORDER BY x ASC, y DESC, и это не то же самое, что ORDER BY x DESC, y DESC.

Здесь выражение_сортировки может быть меткой столбца или номером выводимого столбца, как в данном примере:

    SELECT a + b AS sum, c FROM table1 ORDER BY sum;
    SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
Оба эти запроса сортируют результат по первому столбцу. Заметьте, что имя выводимого столбца должно оставаться само по себе, его нельзя использовать в выражении. Например, это ошибка:

    SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- неправильно
Это ограничение позволяет уменьшить неоднозначность. Тем не менее неоднозначность возможна, когда в ORDER BY указано простое имя, но оно соответствует и имени выходного столбца, и столбцу из табличного выражения. В этом случае используется выходной столбец. Эта ситуация может возникнуть, только когда с помощью AS выходному столбцу назначается то же имя, что имеет столбец в другой таблице.

ORDER BY можно применить к результату комбинации UNION, INTERSECT и EXCEPT, но в этом случае возможна сортировка только по номерам или именам столбцов, но не по выражениям.


> [5] На деле Postgres Pro определяет порядок сортировки для ASC и DESC по классу оператора B-дерева по умолчанию для типа данных выражения. Обычно типы данных создаются так, что этому порядку соответствуют операторы < и >, но возможно разработать собственный тип данных, который будет вести себя по-другому.



# [LIMIT и OFFSET](https://postgrespro.ru/docs/postgrespro/12/queries-limit)

Указания LIMIT и OFFSET позволяют получить только часть строк из тех, что выдал остальной запрос:

    SELECT список_выборки
        FROM табличное_выражение
        [ ORDER BY ... ]
        [ LIMIT { число | ALL } ] [ OFFSET число ]
Если указывается число LIMIT, в результате возвращается не больше заданного числа строк (меньше может быть, если сам запрос выдал меньшее количество строк). LIMIT ALL равносильно отсутствию указания LIMIT, как и LIMIT с аргументом NULL.

**OFFSET** указывает пропустить указанное число строк, прежде чем начать выдавать строки. OFFSET 0 равносильно отсутствию указания OFFSET, как и OFFSET с аргументом NULL.

Если указано и OFFSET, и LIMIT, сначала система пропускает OFFSET строк, а затем начинает подсчитывать строки для ограничения LIMIT.

Применяя LIMIT, важно использовать также предложение ORDER BY, чтобы строки результата выдавались в определённом порядке. Иначе будут возвращаться непредсказуемые подмножества строк. Вы можете запросить строки с десятой по двадцатую, но какой порядок вы имеете в виду? Порядок будет неизвестен, если не добавить ORDER BY.

Оптимизатор запроса учитывает ограничение LIMIT, строя планы выполнения запросов, поэтому вероятнее всего планы (а значит и порядок строк) будут меняться при разных LIMIT и OFFSET. Таким образом, различные значения LIMIT/OFFSET, выбирающие разные подмножества результатов запроса, приведут к несогласованности результатов, если не установить предсказуемую сортировку с помощью ORDER BY. Это не ошибка, а неизбежное следствие того, что SQL не гарантирует вывод результатов запроса в некотором порядке, если порядок не определён явно предложением ORDER BY.

Строки, пропускаемые согласно предложению OFFSET, тем не менее должны вычисляться на сервере. Таким образом, при больших значениях OFFSET работает неэффективно.

# [Списки VALUES](https://postgrespro.ru/docs/postgrespro/12/queries-values)

Предложение VALUES позволяет создать «постоянную таблицу», которую можно использовать в запросе, не создавая и не наполняя таблицу в БД. Синтаксис предложения:

VALUES ( выражение [, ...] ) [, ...]
Для каждого списка выражений в скобках создаётся строка таблицы. Все списки должны иметь одинаковое число элементов (т. е. число столбцов в таблице) и соответствующие элементы во всех списках должны иметь совместимые типы данных. Фактический тип данных столбцов результата определяется по тем же правилам, что и для UNION (см. Раздел 10.5).

Как пример:

VALUES (1, 'one'), (2, 'two'), (3, 'three');
вернёт таблицу из двух столбцов и трёх строк. Это равносильно такому запросу:

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
По умолчанию Postgres Pro назначает столбцам таблицы VALUES имена column1, column2 и т. д. Имена столбцов не определены в стандарте SQL и в другой СУБД они могут быть другими, поэтому обычно лучше переопределить имена списком псевдонимов, например так:

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)
Синтаксически список VALUES с набором выражений равнозначен:

SELECT список_выборки FROM табличное_выражение
и допускается везде, где допустим SELECT. Например, вы можете использовать его в составе UNION или добавить к нему определение_сортировки (ORDER BY, LIMIT и/или OFFSET). VALUES чаще всего используется как источник данных для команды INSERT, а также как подзапрос.

За дополнительными сведениями обратитесь к справке VALUES.


# [Запросы WITH (Общие табличные выражения)](https://postgrespro.ru/docs/postgrespro/12/queries-with)


WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

## SELECT в WITH
Основное предназначение SELECT в предложении WITH заключается в разбиении сложных запросов на простые части. Например, запрос:

    WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
    ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
    )
    SELECT region,
        product,
        SUM(quantity) AS product_units,
        SUM(amount) AS product_sales
    FROM orders
    WHERE region IN (SELECT region FROM top_regions)
    GROUP BY region, product;
выводит итоги по продажам только для передовых регионов. Предложение WITH определяет два дополнительных оператора regional_sales и top_regions так, что результат regional_sales используется в top_regions, а результат top_regions используется в основном запросе SELECT. Этот пример можно было бы переписать без WITH, но тогда нам понадобятся два уровня вложенных подзапросов SELECT. Показанным выше способом это можно сделать немного проще.

Необязательное указание RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE, запрос WITH может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:

    WITH RECURSIVE t(n) AS (
        VALUES (1)
    UNION ALL
        SELECT n+1 FROM t WHERE n < 100
    )
    SELECT sum(n) FROM t;
В общем виде рекурсивный запрос WITH всегда записывается как не рекурсивная часть, потом UNION (или UNION ALL), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса. Такой запрос выполняется следующим образом:

Вычисление рекурсивного запроса

Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.

Пока рабочая таблица не пуста, повторяются следующие действия:

Вычисляется рекурсивная часть так, что рекурсивная ссылка на сам запрос обращается к текущему содержимому рабочей таблицы. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.

Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица очищается.

> Примечание
Строго говоря, этот процесс является итерационным, а не рекурсивным, но комитетом по стандартам SQL был выбран термин RECURSIVE.

В показанном выше примере в рабочей таблице на каждом этапе содержится всего одна строка и в ней последовательно накапливаются значения от 1 до 100. На сотом шаге, благодаря условию WHERE, не возвращается ничего, так что вычисление запроса завершается.

Рекурсивные запросы обычно применяются для работы с иерархическими или древовидными структурами данных. В качестве полезного примера можно привести запрос, находящий все непосредственные и косвенные составные части продукта, используя только таблицу с прямыми связями:

    WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
        SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
    UNION ALL
        SELECT p.sub_part, p.part, p.quantity
        FROM included_parts pr, parts p
        WHERE p.part = pr.sub_part
    )
    SELECT sub_part, SUM(quantity) as total_quantity
    FROM included_parts
    GROUP BY sub_part
Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION вместо UNION ALL, так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, рассмотрите следующий запрос, просматривающий таблицу graph по полю link:

    WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
    UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
    )
    SELECT * FROM search_graph;
Этот запрос зациклится, если связи link содержат циклы. Так как нам нужно получать в результате «depth», одно лишь изменение UNION ALL на UNION не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого мы добавляем два столбца path и cycle и получаем запрос, защищённый от зацикливания:

    WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
        ARRAY[g.id],
        false
        FROM graph g
    UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
        path || g.id,
        g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
    )
    SELECT * FROM search_graph;
Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления «пути», приведшего к определённой строке.

В общем случае, когда для выявления цикла нужно проверять несколько полей, следует использовать массив строк. Например, если нужно сравнить поля f1 и f2:

    WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
        ARRAY[ROW(g.f1, g.f2)],
        false
        FROM graph g
    UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
        path || ROW(g.f1, g.f2),
        ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
    )
    SELECT * FROM search_graph;
    
> Подсказка
Часто для распознавания цикла достаточного одного поля и тогда ROW() можно опустить. При этом будет использоваться не массив данных составного типа, а простой массив, что более эффективно.

> Подсказка
Этот алгоритм рекурсивного вычисления запроса выдаёт в результате узлы, упорядоченные по пути погружения. Чтобы получить результаты, отсортированные по глубине, можно добавить во внешний запрос ORDER BY по столбцу «path», полученному, как показано выше.

Для тестирования запросов, которые могут зацикливаться, есть хороший приём — добавить LIMIT в родительский запрос. Например, следующий запрос зациклится, если не добавить предложение LIMIT:

    WITH RECURSIVE t(n) AS (
        SELECT 1
    UNION ALL
        SELECT n+1 FROM t
    )
    SELECT n FROM t LIMIT 100;
Но в данном случае этого не происходит, так как в Postgres Pro запрос WITH выдаёт столько строк, сколько фактически принимает родительский запрос. В производственной среде использовать этот приём не рекомендуется, так как другие системы могут вести себя по-другому. Кроме того, это не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с другой таблицей, так как в подобных случаях внешний запрос обычно всё равно выбирает результат запроса WITH полностью.

Запросы WITH имеют полезное свойство — обычно они вычисляются только раз для всего родительского запроса, даже если этот запрос или соседние запросы WITH обращаются к ним неоднократно. Таким образом, сложные вычисления, результаты которых нужны в нескольких местах, можно выносить в запросы WITH в целях оптимизации. Кроме того, такие запросы позволяют избежать нежелательных вычислений функций с побочными эффектами. Однако есть и обратная сторона — оптимизатор не может распространить ограничения родительского запроса на неоднократно задействуемый запрос WITH, так как это может повлиять на использование результата WITH во всех местах, тогда как должно повлиять только в одном. Многократно задействуемый запрос WITH будет выполняться буквально и возвращать все строки, включая те, что потом может отбросить родительский запрос. (Но как было сказано выше, вычисление может остановиться раньше, если в ссылке на этот запрос затребуется только ограниченное число строк.)

Однако если запрос WITH является нерекурсивным и свободным от побочных эффектов (то есть это SELECT, не вызывающий изменчивых функций), он может быть свёрнут в родительский запрос, что позволит оптимизировать совместно два уровня запросов. По умолчанию это происходит, только если запрос WITH задействуется в родительском запросе всего в одном месте, а не в нескольких. Это поведение можно переопределить, добавив указание MATERIALIZED, чтобы выделить вычисление запроса WITH, или указание NOT MATERIALIZED, чтобы принудительно свернуть его в родительский запрос. В последнем случае возникает риск многократного вычисления запроса WITH, но в итоге это может быть выгодно, если в каждом случае использования WITH из всего результата запроса остаётся только небольшая часть.

Простой пример для демонстрации этих правил:

    WITH w AS (
        SELECT * FROM big_table
    )
    SELECT * FROM w WHERE key = 123;
    Этот запрос WITH будет свёрнут в родительский и будет выполнен с тем же планом, что и запрос:

    SELECT * FROM big_table WHERE key = 123;
В частности, если в таблице создан индекс по столбцу key, он может использоваться для получения строк с key = 123. В другом случае:

    WITH w AS (
        SELECT * FROM big_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
    WHERE w2.key = 123;
запрос WITH будет материализован, то есть создастся временная копия таблицы big_table, которая будет соединена с собой же, без использования какого-либо индекса. Этот запрос будет выполняться гораздо эффективнее в таком виде:

    WITH w AS NOT MATERIALIZED (
        SELECT * FROM big_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
    WHERE w2.key = 123;
В этом случае ограничения родительского запроса могут применяться непосредственно при сканировании big_table.

Пример, в котором вариант NOT MATERIALIZED может быть нежелательным:

    WITH w AS (
        SELECT key, very_expensive_function(val) as f FROM some_table
    )
    SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
В данном случае благодаря материализации запроса WITH ресурсоёмкая функция very_expensive_function вычисляется только один раз для строки таблицы, а не дважды.

Примеры выше показывают только предложение WITH с SELECT, но таким же образом его можно использовать с командами INSERT, UPDATE и DELETE. В каждом случае он по сути создаёт временную таблицу, к которой можно обратиться в основной команде.

## Изменение данных в WITH
В предложении WITH можно также использовать операторы, изменяющие данные (INSERT, UPDATE или DELETE). Это позволяет выполнять в одном запросе сразу несколько разных операций. Например:

    WITH moved_rows AS (
        DELETE FROM products
        WHERE
            "date" >= '2010-10-01' AND
            "date" < '2010-11-01'
        RETURNING *
    )
    INSERT INTO products_log
    SELECT * FROM moved_rows;
Этот запрос фактически перемещает строки из products в products_log. Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое в предложении RETURNING; а затем главный запрос читает это содержимое и вставляет в таблицу products_log.

Следует заметить, что предложение WITH в данном случае присоединяется к оператору INSERT, а не к SELECT, вложенному в INSERT. Это необходимо, так как WITH может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH, так что к результату WITH можно обратиться и из вложенного оператора SELECT.

Операторы, изменяющие данные, в WITH обычно дополняются предложением RETURNING (см. Раздел 6.4), как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING, а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH не дополнен предложением RETURNING, временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:

    WITH t AS (
        DELETE FROM foo
    )
    DELETE FROM bar;
Он удалит все строки из таблиц foo и bar. При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar.

Рекурсивные ссылки в операторах, изменяющих данные, не допускаются. В некоторых случаях это ограничение можно обойти, обратившись к конечному результату рекурсивного WITH, например так:

    WITH RECURSIVE included_parts(sub_part, part) AS (
        SELECT sub_part, part FROM parts WHERE part = 'our_product'
    UNION ALL
        SELECT p.sub_part, p.part
        FROM included_parts pr, parts p
        WHERE p.part = pr.sub_part
    )
    DELETE FROM parts
    WHERE part IN (SELECT part FROM included_parts);
Этот запрос удаляет все непосредственные и косвенные составные части продукта.

Операторы, изменяющие данные в WITH, выполняются только один раз и всегда полностью, вне зависимости от того, принимает ли их результат основной запрос. Заметьте, что это отличается от поведения SELECT в WITH: как говорилось в предыдущем разделе, SELECT выполняется только до тех пор, пока его результаты востребованы основным запросом.

Вложенные операторы в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, порядок, в котором операторы в WITH будут фактически изменять данные, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. Главу 13), так что они не могут «видеть», как каждый из них меняет целевые таблицы. Это уменьшает эффект непредсказуемости фактического порядка изменения строк и означает, что RETURNING — единственный вариант передачи изменений от вложенных операторов WITH основному запросу. Например, в данном случае:

    WITH t AS (
        UPDATE products SET price = price * 1.05
        RETURNING *
    )
    SELECT * FROM products;
    внешний оператор SELECT выдаст цены, которые были до действия UPDATE, тогда как в запросе

    WITH t AS (
        UPDATE products SET price = price * 1.05
        RETURNING *
    )
    SELECT * FROM t;
внешний SELECT выдаст изменённые данные.

Неоднократное изменение одной и той же строки в рамках одного оператора не поддерживается. Иметь место будет только одно из нескольких изменений и надёжно определить, какое именно, часто довольно сложно (а иногда и вовсе невозможно). Это так же касается случая, когда строка удаляется и изменяется в том же операторе: в результате может быть выполнено только обновление. Поэтому в общем случае следует избегать подобного наложения операций. В частности, избегайте подзапросов WITH, которые могут повлиять на строки, изменяемые основным оператором или операторами, вложенные в него. Результат действия таких запросов будет непредсказуемым.

В настоящее время, для оператора, изменяющего данные в WITH, в качестве целевой нельзя использовать таблицу, для которой определено условное правило или правило ALSO или INSTEAD, если оно состоит из нескольких операторов.
